﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Windows.Forms;
using System.IO;
namespace miscfun
{
    public class Class1
    {  
        public static void loadStateData(ComboBox cmbstate)
        {
            using (SqlConnection con = new SqlConnection(connection()))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT DISTINCT State FROM dbo.pincoderec ORDER BY State asc", con);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    cmbstate.Items.Add(dr["state"].ToString());
                }
            }
        }
        public static void loadDistictData(ListView lstDistict, string state)
        {
            using (SqlConnection con = new SqlConnection(connection()))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("spGetDistict", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter prmState = new SqlParameter("@STATE",state);
                cmd.Parameters.Add(prmState);

                SqlDataReader dr = cmd.ExecuteReader();
                lstDistict.Items.Clear();
                while (dr.Read())
                {
                    lstDistict.Items.Add(dr["Districts Name"].ToString());
                }
            }
        }
        public static void loadPostOffice(string state, string distict,ComboBox cmbPO)
        {
            using (SqlConnection con = new SqlConnection(connection()))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("sgGetPostOffice", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter prmState = new SqlParameter("@State", state);
                cmd.Parameters.Add(prmState);

                SqlParameter prmDistict = new SqlParameter("@Distict", distict);
                cmd.Parameters.Add(prmDistict);

                SqlDataReader dr = cmd.ExecuteReader();
                cmbPO.Items.Clear();
                cmbPO.Text = "";
                while (dr.Read())
                {
                    cmbPO.Items.Add(dr["Post Office Name"].ToString());
                }
            }
        }
        public static void displayPinCode(string state, string distict, string po, TextBox txtpincode)
        {
            using (SqlConnection con = new SqlConnection(connection()))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("spGetPinCode", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter prmState = new SqlParameter("@State", state);
                cmd.Parameters.Add(prmState);

                SqlParameter prmDistict = new SqlParameter("@Distict", distict);
                cmd.Parameters.Add(prmDistict);

                SqlParameter prmPO = new SqlParameter("@PO", po);
                cmd.Parameters.Add(prmPO);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    txtpincode.Text = dr["Pincode"].ToString();
                }
            }
        }
        public static string connection()
        {
            string cs = ConfigurationManager.ConnectionStrings["DBCSPO"].ConnectionString;
            return cs;
        }
        public static void countDistict(string state,ToolStripLabel tslDistict)
        {
            using(SqlConnection con=new SqlConnection(connection()))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("spCountDistict", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter prmState = new SqlParameter("@State", state);
                cmd.Parameters.Add(prmState);

                SqlDataReader cdr = cmd.ExecuteReader();

                while (cdr.Read())
                {
                    tslDistict.Text = "Total Distict In " + state.ToUpper() + " State = " + Convert.ToInt32(cdr["Total Distict"]).ToString();
                }
            }
        }
        public static void countPO(string state, string distict, ToolStripLabel tslPO)
        {
            using (SqlConnection con = new SqlConnection(connection()))
            {
                con.Open();

                SqlCommand cmd = new SqlCommand("spCountPostOffice", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter prmState = new SqlParameter("@State", state);
                cmd.Parameters.Add(prmState);

                SqlParameter prmDistict = new SqlParameter("@Distict", distict);
                cmd.Parameters.Add(prmDistict);

                SqlDataReader cdr = cmd.ExecuteReader();

                while (cdr.Read())
                {
                    tslPO.Text = "Total Post Office in " + distict.ToUpper() +  " Distict = " + Convert.ToInt32(cdr["Total PO"]).ToString();
                }
            }
        }
        public static void loadDrive(ComboBox cmb)
        {
            foreach (var drive in DriveInfo.GetDrives())
            {
                cmb.Items.Add(drive);
            }
        }
        public static string CreateRootDir(string path,ComboBox cmbstate,Label lbldistict)
        {
            string msg = "";
            string statepath = "";
            string distictpath = "";
            if (!(Directory.Exists(path)))
            {
                Directory.CreateDirectory(path);
                DirectoryInfo drstate = new DirectoryInfo(path);
                if (!(drstate.Exists))
                {
                    statepath = path + @"\" + cmbstate.SelectedItem.ToString();
                    drstate.CreateSubdirectory(path);
                    DirectoryInfo drdistict = new DirectoryInfo(statepath);
                    if (!(drdistict.Exists))
                    {
                        distictpath = statepath + @"\" + lbldistict.Text;
                        drdistict.CreateSubdirectory(distictpath);
                        msg = "Directory Created";
                    }
                }
            }
            return msg;
        }
    }
}
